package de.home.db;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;

import de.home.db.model.Floor;
import de.home.db.model.RemotePowerPoint;
import de.home.db.model.Room;
import de.home.util.Configuration;

public class DBService {

   private static final Logger LOG       = LogManager.getLogger(DBService.class);
   private static DBService    dbService = null;

   public Connection getConnection() throws SQLException {
      Connection connection;
      // setup the connection with the DB.
      String dbUserName = Configuration.getInstance().getString("dbUserName");
      String dbUserPassword = Configuration.getInstance().getString("dbUserPassword");
      String dbName = Configuration.getInstance().getString("dbName");
      // String connString = "jdbc:mysql://127.0.0.1/" + dbName + "?" +
      // "user=" + dbUserName + "&password="
      // + dbUserPassword;
      // System.out.println(connString);
      // connection = DriverManager.getConnection(connString);
      connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1/" + dbName, dbUserName, dbUserPassword);
      LOG.trace("Connected to mysql db!");
      return connection;
   }

   private DBService() {
      try {
         initDBConnection();
      } catch (ClassNotFoundException e) {
         LOG.fatal(e.getMessage());
      } catch (SQLException e) {
         LOG.fatal(e.getMessage());
      } catch (Throwable e) {
         LOG.warn(e.getMessage());
      }
   }

   public synchronized static DBService getInstance() {
      if ( dbService == null ) {
         dbService = new DBService();
      }
      return dbService;
   }

   private void initDBConnection() throws ClassNotFoundException, SQLException {
      // this will load the MySQL driver, each DB has its own driver
      Class.forName("com.mysql.jdbc.Driver");

   }

   // private void writeMetaData(ResultSet resultSet) throws SQLException {
   // // now get some metadata from the database
   // System.out.println("The columns in the table are: ");
   // System.out.println("Table: " + resultSet.getMetaData().getTableName(1));
   // for (int i = 1; i <= resultSet.getMetaData().getColumnCount(); i++) {
   // System.out.println("Column " + i + " " +
   // resultSet.getMetaData().getColumnName(i));
   // }
   // }

   public void deleteAllRows(String tableName) throws SQLException {
      PreparedStatement prepareStatement = getConnection().prepareStatement("delete from " + tableName + " where true");
      prepareStatement.execute();
   }

   public void close() {
      try {
         getConnection().close();
      } catch (Exception e) {
         e.printStackTrace();
      }
   }

   public List<Floor> getFloorsWithoutRooms() throws SQLException {
      List<Floor> floors = new ArrayList<Floor>();
      Statement statement = getConnection().createStatement();
      statement.execute("select * from Stockwerke");

      ResultSet resultSet = statement.getResultSet();
      while (resultSet.next()) {
         int id = resultSet.getInt("id");
         String name = resultSet.getString("name");
         floors.add(new Floor(id, name, null));
      }

      return floors;
   }

   public List<Floor> getFloors() throws SQLException {
      List<Floor> floors = new ArrayList<Floor>();
      Statement statement = getConnection().createStatement();
      statement.execute("select * from Stockwerke");

      ResultSet resultSet = statement.getResultSet();
      while (resultSet.next()) {
         int id = resultSet.getInt("id");
         String name = resultSet.getString("name");
         List<Room> rooms = getRooms(id);
         floors.add(new Floor(id, name, rooms));
      }

      return floors;
   }

   public List<Room> getRooms(int floorId) throws SQLException {
      List<Room> rooms = new ArrayList<Room>();
      Statement statement = getConnection().createStatement();
      statement.execute("select * from Zimmer z where z.stockwerk_id =" + floorId);
      ResultSet resultSet = statement.getResultSet();
      while (resultSet.next()) {
         int id = resultSet.getInt("id");
         String name = resultSet.getString("name");
         List<RemotePowerPoint> rpp = getRemotePowerPoints(id);
         rooms.add(new Room(id, name, rpp));
      }
      return rooms;
   }

   public List<RemotePowerPoint> getRemotePowerPoints(int zimmerId) throws SQLException {
      List<RemotePowerPoint> remotePowerPoints = new ArrayList<RemotePowerPoint>();
      Statement statement = getConnection().createStatement();
      statement.execute("select * from Funksteckdosen f where f.zimmer_id =" + zimmerId);
      ResultSet resultSet = statement.getResultSet();
      while (resultSet.next()) {
         int id = resultSet.getInt("id");
         String geraetename = resultSet.getString("geraetename");
         String hauscode = resultSet.getString("hauscode");
         int geraetenummer = resultSet.getInt("geraetenummer");
         String hersteller = resultSet.getString("hersteller");
         remotePowerPoints.add(new RemotePowerPoint(id, geraetename, hauscode, geraetenummer, hersteller));
      }

      return remotePowerPoints;
   }

   public void createNewFloor(String newName) throws SQLException {
      PreparedStatement prepareStatement = getConnection().prepareStatement(
            "insert into Stockwerke(name) values('" + newName + "')");
      prepareStatement.executeUpdate();
   }

   public void createNewRoom(String newName, int sID) throws SQLException {
      PreparedStatement prepareStatement = getConnection().prepareStatement(
            "insert into Zimmer(name,stockwerk_id) values('" + newName + "','" + sID + "')");
      prepareStatement.executeUpdate();
      LOG.warn("name input db = {}", newName);
   }

   public void createNewRemotePowerPoint(String geraetename, String hauscode, int geraetenummer, String hersteller,
         int zimmer_id) throws SQLException {
      PreparedStatement prepareStatement = getConnection().prepareStatement(
            "insert into Funksteckdosen(geraetename,hauscode,geraetenummer,hersteller,zimmer_id) values(?,?,?,?,?)");
      prepareStatement.setString(1, geraetename);
      prepareStatement.setString(2, hauscode);
      prepareStatement.setInt(3, geraetenummer);
      prepareStatement.setString(4, hersteller);
      prepareStatement.setInt(5, zimmer_id);
      prepareStatement.executeUpdate();
   }

   public void removeFloor(int floorId) throws SQLException {
      removeEntry("Stockwerke", floorId);
   }

   public void removeRoom(int roomId) throws SQLException {
      removeEntry("Zimmer", roomId);
   }

   public void removeRemotePowerPoint(int rppId) throws SQLException {
      removeEntry("Funksteckdosen", rppId);
   }

   private void removeEntry(String tableName, int id) throws SQLException {
      PreparedStatement prepareStatement = getConnection().prepareStatement(
            "delete from " + tableName + " where id =" + id);
      prepareStatement.execute();
   }

   public void editRemotePowerPoint(int rppId, String geraetename, String hauscode, int geraetenummer, String hersteller)
         throws SQLException {
      PreparedStatement prepareStatement = getConnection().prepareStatement(
            "UPDATE Funksteckdosen SET geraetename=?, hauscode=?, geraetenummer=?, hersteller=? WHERE id=?");
      prepareStatement.setString(1, geraetename);
      prepareStatement.setString(2, hauscode);
      prepareStatement.setInt(3, geraetenummer);
      prepareStatement.setString(4, hersteller);
      prepareStatement.setInt(5, rppId);
      prepareStatement.executeUpdate();
   }

   public RemotePowerPoint getRemotePowerPoint(int id) throws SQLException {
      Statement statement = getConnection().createStatement();
      statement.execute("select * from Funksteckdosen f where f.id =" + id);
      ResultSet resultSet = statement.getResultSet();
      while (resultSet.next()) {
         int rid = resultSet.getInt("id");
         String geraetename = resultSet.getString("geraetename");
         String hauscode = resultSet.getString("hauscode");
         int geraetenummer = resultSet.getInt("geraetenummer");
         String hersteller = resultSet.getString("hersteller");
         return new RemotePowerPoint(rid, geraetename, hauscode, geraetenummer, hersteller);
      }

      return null;
   }

   public RemotePowerPoint getRemotePowerPoint(String name) throws SQLException {
      Statement statement = getConnection().createStatement();
      statement.execute("select * from Funksteckdosen f where f.geraetename ='" + name + "'");
      ResultSet resultSet = statement.getResultSet();
      while (resultSet.next()) {
         int rid = resultSet.getInt("id");
         String geraetename = resultSet.getString("geraetename");
         String hauscode = resultSet.getString("hauscode");
         int geraetenummer = resultSet.getInt("geraetenummer");
         String hersteller = resultSet.getString("hersteller");
         return new RemotePowerPoint(rid, geraetename, hauscode, geraetenummer, hersteller);
      }

      return null;
   }

   public void renameFloor(int id, String newNameg) throws SQLException {
      PreparedStatement prepareStatement = getConnection().prepareStatement("UPDATE Stockwerke SET name=? WHERE id=?");
      prepareStatement.setString(1, newNameg);
      prepareStatement.setInt(2, id);
      prepareStatement.executeUpdate();
   }

}
